# systematically queries StockStatusDB and calculates 
# average buyS and sellS


calcSSCombinations <- function(con, tblname, verbose=TRUE) {
  if (verbose==TRUE) cat("start calcSSCombinations, tbl ",tblname," : ", format(Sys.time(), "%Y-%m-%d %X"), "\n")
  createMat <- function(lbcols) { # function creates lbcols dimension matrix
    mat <- matrix(0, nrow=(2^lbcols), ncol=lbcols)
    j <- lbcols-1
    for (i in 1:lbcols) {
      mat[,i] = rep(rep(0:1, each=(2^(i-1))), (2^(j)))
      j <- j-1
    }
    return (mat)    
  }
  sqlcmd <- function(row) {
    sql <- sprintf("SELECT * FROM `%s` WHERE", tblname)
    wherec <- c()
    for (i in 1:lbcols) {
      if (row[i] == 1) wherec <- c(wherec, bcols[i])
    }
    sql <- paste(sql, paste(sprintf("%s = 1",wherec), collapse=" AND "))
    return (sql)
  }
  cols <- dbListFields(con, tblname)
  bcols <- cols[grep("^b_", cols)]
  lbcols <- length(bcols)
  if (verbose==TRUE) cat("calculating ", lbcols, " binary columns.\n")
  
  # create database _an
  createVadbTable_an(con, tblname, bcols)
  
  mat <- createMat(lbcols)
  for (i in 2:(dim(mat)[1])) {
    tbl <- dbGetQuery(con, sqlcmd(mat[i,]))
    dimtbl <- dim(tbl)
    # omit constellations with less than 5 observations
    if (dimtbl[1] > 5) {
      c0_BSSummary <- summary(tbl[,"d_c0_buyS"])
      c0_SSSummary <- summary(tbl[,"d_c0_sellS"])
      o1_BSSummary <- summary(tbl[,"d_o1_buyS"])
      o1_SSSummary <- summary(tbl[,"d_o1_sellS"])
      c1_BSSummary <- summary(tbl[,"d_c1_buyS"])
      c1_SSSummary <- summary(tbl[,"d_c1_sellS"])
      fillVadbTable_an(con, tblname, (i-1), dimtbl[1], bcols, mat[i,], 
                       c0_BSSummary, c0_SSSummary, 
                       o1_BSSummary, o1_SSSummary, 
                       c1_BSSummary, c1_SSSummary)
    }
  }
  if (verbose==TRUE) cat("stop calcSSCombinations : ", format(Sys.time(), "%Y-%m-%d %X"), "\n")
}